<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class mdl_daftar_surat extends CI_Model {
    
	function __construct(){
        parent::__construct();
    }

    function getdata($plimit=true){
		# get parameter from easy grid
		$page = isset($_POST['page']) ? intval($_POST['page']) : 1;  
		$limit = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
		$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'id_surat';  
		$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';  
		$offset = ($page-1)*$limit;
		
		#get filter
		$jenis_surat = isset($_POST['jenis_surat']) ? strval($_POST['jenis_surat']) : '';
		$nama_warga = isset($_POST['nama_warga']) ? strval($_POST['nama_warga']) : '';
		
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('*, date_format(tanggal,"%d/%m/%Y") as tgl_surat', false);
			$this->db->from('surat');
			

			if($jenis_surat != ''){
				$this->db->where('jenis_surat', $jenis_surat);
			}

			if($nama_warga != ''){
				$this->db->like('nama_warga', $nama_warga, 'both');
			}
			
			$this->db->order_by($sort, $order);
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data
		if($plimit == true){
			$this->db->limit($limit, $offset);
		}
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
	}

	function togrid($data, $count=0){
		if($count>0){
			$i=0;
			foreach($data->result_array() as $row){
				foreach($row as $key => $value){
					$response->rows[$i][$key]=$value;
				}
				$i++;
			}
			$response->total = $count;
		}else{
		$response->rows = array();
		}
		return json_encode($response);
	}

	function getDataCount(){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('surat');
		
		return $this->db->get()->num_rows();
	}
	
	
	# Get Nomor
	function getNoTerakhir(){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('surat');
		
		$this->db->where('jenis_surat', 'SK');

		return $this->db->get()->num_rows();
	}

	function getNoTerakhirSKP(){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('surat');
		
		$this->db->where('jenis_surat', 'SKP');

		return $this->db->get()->num_rows();
	}

	function getNoTerakhirSKA(){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('surat');
		
		$this->db->where('jenis_surat', 'SKA');

		return $this->db->get()->num_rows();
	}

	function getNoTerakhirSKD(){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('surat');
		
		$this->db->where('jenis_surat', 'SKD');

		return $this->db->get()->num_rows();
	}

	function getNoTerakhirSKU(){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('surat');
		
		$this->db->where('jenis_surat', 'SKU');

		return $this->db->get()->num_rows();
	}	

	function getNoTerakhirSL(){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('surat');
		
		$this->db->where('jenis_surat', 'SL');

		return $this->db->get()->num_rows();
	}

	function getNoTerakhirSM(){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('surat');
		
		$this->db->where('jenis_surat', 'SM');

		return $this->db->get()->num_rows();
	}

	# End of Get Nomor

	function getDataByktp($no_ktp){
		$this->db->flush_cache();
		$this->db->select('*');
		$this->db->from('warga');
		
		$this->db->where('no_ktp', $no_ktp);
		
		return $this->db->get();
	}

	# Save Surat

	public function save_sk($data){
		/*ambil tanda tangan */
		$td = $this->db->query("SELECT * FROM ttd WHERE id_ttd='".$data['tanda_tangan']."'");

		$this->db->set('jenis_surat', 'SK');
		$this->db->set('no_surat', $data['nomer_surat']);
		$this->db->set('nama_surat', 'Surat Keterangan');
		$this->db->set('tanggal', date('Y-m-d'));
		$this->db->set('alamat', $data['alamat']);
		$this->db->set('id_warga', $data['no_ktp']);
		$this->db->set('nama_warga', $data['nama']);
		$this->db->set('isi_surat', json_encode($data));
		$this->db->set('tanda_tangan', json_encode($td->result_array()));

		return $this->db->insert('surat');
	}

	public function save_skp($data){
		/*ambil tanda tangan*/
		$td = $this->db->query("SELECT * FROM ttd WHERE id_ttd='".$data['tanda_tangan']."'");


		$this->db->set('jenis_surat', 'SKP');
		$this->db->set('no_surat', $data['nomer_surat']);
		$this->db->set('nama_surat', 'Surat Keterangan Pindah');
		$this->db->set('tanggal', date('Y-m-d'));
		$this->db->set('alamat', $data['alamat']);
		$this->db->set('alasan', $data['alasan']);
		$this->db->set('pindah_ke', $data['pindah_ke']);
		$this->db->set('id_warga', $data['no_ktp']);
		$this->db->set('nama_warga', $data['nama']);
		$this->db->set('isi_surat', json_encode($data));
		$this->db->set('tanda_tangan', json_encode($td->result_array()));

		return $this->db->insert('surat');
	}

	public function save_ska($data){
		/*ambil tanda tangan*/
		$td = $this->db->query("SELECT * FROM ttd WHERE id_ttd='".$data['tanda_tangan']."'");

		$this->db->set('jenis_surat', 'SKA');
		$this->db->set('no_surat', $data['nomer_surat']);
		$this->db->set('nama_surat', 'Surat Keterangan Adat Istiadat');
		$this->db->set('tanggal', date('Y-m-d'));
		$this->db->set('alamat', $data['alamat']);
		$this->db->set('id_warga', $data['no_ktp']);
		$this->db->set('nama_warga', $data['nama']);
		$this->db->set('isi_surat', json_encode($data));
		$this->db->set('tanda_tangan', json_encode($td->result_array()));

		return $this->db->insert('surat');
	}

	public function save_skd($data){
		/*ambil tanda tangan */
		$td = $this->db->query("SELECT * FROM ttd WHERE id_ttd='".$data['tanda_tangan']."'");

		$this->db->set('jenis_surat', 'SKD');
		$this->db->set('no_surat', $data['nomer_surat']);
		$this->db->set('nama_surat', 'Surat Keterangan Domisili');
		$this->db->set('tanggal', date('Y-m-d'));
		$this->db->set('alamat', $data['alamat']);
		$this->db->set('id_warga', $data['no_ktp']);
		$this->db->set('nama_warga', $data['nama']);
		$this->db->set('isi_surat', json_encode($data));
		$this->db->set('tanda_tangan', json_encode($td->result_array()));

		return $this->db->insert('surat');
	}

	public function save_sku($data){
		/*ambil tanda tangan */
		$td = $this->db->query("SELECT * FROM ttd WHERE id_ttd='".$data['tanda_tangan']."'");

		$this->db->set('jenis_surat', 'SKU');
		$this->db->set('no_surat', $data['nomer_surat']);
		$this->db->set('nama_surat', 'Surat Keterangan Usaha');
		$this->db->set('tanggal', date('Y-m-d'));
		$this->db->set('alamat', $data['alamat']);
		$this->db->set('id_warga', $data['no_ktp']);
		$this->db->set('nama_warga', $data['nama']);
		$this->db->set('isi_surat', json_encode($data));
		$this->db->set('tanda_tangan', json_encode($td->result_array()));

		return $this->db->insert('surat');
	}	

	public function save_skl($data){
		$this->db->set('jenis_surat', 'SK');
		$this->db->set('no_surat', $data['nomer_surat']);
		$this->db->set('nama_surat', 'Surat Keterangan Domisili');
		$this->db->set('tanggal', date('Y-m-d'));
		$this->db->set('id_warga', $data['no_ktp']);
		$this->db->set('nama_warga', $data['nama']);
		$this->db->set('isi_surat', json_encode($data));
		$this->db->set('tanda_tangan', '');

		return $this->db->insert('surat');
	}

	public function save_sl($data){
		/*ambil tanda tangan */
		$td = $this->db->query("SELECT * FROM ttd WHERE id_ttd='".$data['tanda_tangan']."'");
		
		$this->db->set('jenis_surat', 'SL');
		$this->db->set('no_surat', $data['nomer_surat']);
		$this->db->set('nama_surat', 'Surat Keterangan Lahir');
		$this->db->set('tanggal', date('Y-m-d'));
		$this->db->set('alamat', $data['alamat']);
		$this->db->set('nama_warga', $data['nama']);
		$this->db->set('isi_surat', json_encode($data));
		$this->db->set('tanda_tangan', json_encode($td->result_array()));

		return $this->db->insert('surat');
	}

	public function save_sm($data){
		/*ambil tanda tangan */
		$td = $this->db->query("SELECT * FROM ttd WHERE id_ttd='".$data['tanda_tangan']."'");
		

		$this->db->trans_start();

		#update table warga

		$this->db->flush_cache();
		$this->db->set('status', '0');
		$this->db->where('no_ktp', $data['no_ktp']);
		$this->db->update('warga');

		#insert tabel surat
		$this->db->flush_cache();
		$this->db->set('jenis_surat', 'SM');
		$this->db->set('no_surat', $data['nomer_surat']);
		$this->db->set('nama_surat', 'Surat Keterangan Meninggal');
		$this->db->set('id_warga', $data['no_ktp']);
		$this->db->set('tanggal', date('Y-m-d'));
		$this->db->set('alamat', $data['alamat']);
		$this->db->set('nama_warga', $data['nama']);
		$this->db->set('isi_surat', json_encode($data));
		$this->db->set('tanda_tangan', json_encode($td->result_array()));

		$this->db->insert('surat');

		$this->db->trans_complete();
	    return $this->db->trans_status();

	}

	# End Of Save Surat

	public function get_surat_by_nomer($no_surat){
		$this->db->where('no_surat', $no_surat);
		return $this->db->get('surat');
	}

	public function getKonten(){
		$this->db->where('jenis_surat','SK');
		return $this->db->get('konten');
	}

	public function getKontenSKA(){
		$this->db->where('jenis_surat','SKA');
		return $this->db->get('konten');
	}

	public function getKontenSKP(){
		$this->db->where('jenis_surat','SKP');
		return $this->db->get('konten');
	}

	public function getKontenSKU(){
		$this->db->where('jenis_surat','SKU');
		return $this->db->get('konten');
	}

	public function getKontenSKD(){
		$this->db->where('jenis_surat','SKD');
		return $this->db->get('konten');
	}

	public function getKontenSL(){
		$this->db->where('jenis_surat','SL');
		return $this->db->get('konten');
	}

	public function getKontenSM(){
		$this->db->where('jenis_surat','SM');
		return $this->db->get('konten');
	}
}

?>